package com.amado.course45mysql09other;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.Arrays;
import java.util.List;

import com.amado.course45mysql02.dao.DBConnection;
import com.amado.course45mysql02.domain.Student;

public class EmployeeRollbackDao {


    /**
     * 自动提交事务
     *
     * @param studentList
     */
    public void addBatchAutoCommit(List<Student> studentList) {
        Connection conn = DBConnection.getConn();
        String sql = "insert into t_student(name,sex,phone,age)values(?,?,?,?)";
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            ps1 = conn.prepareStatement(sql);
            ps1.setString(1, studentList.get(0).getName());
            ps1.setByte(2, studentList.get(0).getSex());
            ps1.setString(3, studentList.get(0).getPhone());
            ps1.setShort(4, studentList.get(0).getAge());
            ps1.execute();


            ps2 = conn.prepareStatement(sql);
            ps2.setString(1, studentList.get(1).getName());
            ps2.setByte(2, studentList.get(1).getSex());
            ps2.setString(3, studentList.get(1).getPhone());
            ps2.setShort(4, studentList.get(1).getAge());
            ps2.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(null, ps1, conn);
            DBConnection.close(null, ps2, null);
        }
    }

    /**
     * 手功提交事务，不提交
     *
     * @param studentList
     */
    public void addBatchNotCommit(List<Student> studentList) {
        Connection conn = DBConnection.getConn();
        String sql = "insert into t_student(name,sex,phone,age)values(?,?,?,?)";
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
        	//自动提交设置为false
            conn.setAutoCommit(false);
            ps1 = conn.prepareStatement(sql);
            ps1.setString(1, studentList.get(0).getName());
            ps1.setByte(2, studentList.get(0).getSex());
            ps1.setString(3, studentList.get(0).getPhone());
            ps1.setShort(4, studentList.get(0).getAge());
            ps1.execute();


            ps2 = conn.prepareStatement(sql);
            ps2.setString(1, studentList.get(1).getName());
            ps2.setByte(2, studentList.get(1).getSex());
            ps2.setString(3, studentList.get(1).getPhone());
            ps2.setShort(4, studentList.get(1).getAge());
            ps2.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(null, ps1, conn);
            DBConnection.close(null, ps2, null);
        }
    }

    /**
     * 事务回滚所有
     *
     * @param studentList
     */
    public void addBatchRollback(List<Student> studentList) {
        Connection conn = DBConnection.getConn();
        String sql = "insert into t_student(name,sex,phone,age)values(?,?,?,?)";
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
        	
            conn.setAutoCommit(false);
            ps1 = conn.prepareStatement(sql);
            ps1.setString(1, studentList.get(0).getName());
            ps1.setByte(2, studentList.get(0).getSex());
            ps1.setString(3, studentList.get(0).getPhone());
            ps1.setShort(4, studentList.get(0).getAge());
            ps1.execute();


            ps2 = conn.prepareStatement(sql);
            ps2.setString(1, studentList.get(1).getName());
            ps2.setByte(2, studentList.get(1).getSex());
            ps2.setString(3, studentList.get(1).getPhone());
            ps2.setShort(4, studentList.get(1).getAge());
            ps2.execute();
            //手工提交事务
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
                try {
					conn.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
        } finally {
            DBConnection.close(null, ps1, conn);
            DBConnection.close(null, ps2, null);
        }
    }

    /**
     * 回滚到事务点
     * @param studentList
     */
    public void addBatchPoint(List<Student> studentList) {
        Connection conn = DBConnection.getConn();
        String sql = "insert into t_student(name,sex,phone,age)values(?,?,?,?)";
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        PreparedStatement ps3 = null;
        Savepoint savepoint = null;
        try {
        	//自动提交设置为false
            conn.setAutoCommit(false);
            ps1 = conn.prepareStatement(sql);
            ps2 = conn.prepareStatement(sql);
            ps3 = conn.prepareStatement(sql);
            //0
            ps1.setString(1, studentList.get(0).getName());
            ps1.setByte(2, studentList.get(0).getSex());
            ps1.setString(3, studentList.get(0).getPhone());
            ps1.setShort(4, studentList.get(0).getAge());
            ps1.execute();
            savepoint = conn.setSavepoint("savePint");
            //1
            ps2.setString(1, studentList.get(1).getName());
            ps2.setByte(2, studentList.get(1).getSex());
            ps2.setString(3, studentList.get(1).getPhone());
            ps2.setShort(4, studentList.get(1).getAge());
            ps2.execute();
            //保存点

            //2
            ps3.setString(1, studentList.get(2).getName());
            ps3.setByte(2, studentList.get(2).getSex());
            ps3.setString(3, studentList.get(2).getPhone());
            ps3.setShort(4, studentList.get(2).getAge());
            ps3.execute();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                //回滚到指定保存点
                conn.rollback(savepoint);
                conn.commit();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DBConnection.close(null, ps1, null);
            DBConnection.close(null, ps2, null);
            DBConnection.close(null, ps3, conn);
        }
    }

    public static void main(String[] args) {
        EmployeeRollbackDao employeeDao = new EmployeeRollbackDao();
//        employeeDao.addBatchAutoCommit(Arrays.asList(new Student("李四baa", (byte) 1, "18578223322", (short) 20), new Student("李四cc111111111111111111111111", (byte) 1, "18578223322", (short) 20)));
//        employeeDao.addBatchNotCommit(Arrays.asList(new Student("李四baa", (byte) 1, "18578223322", (short) 20), new Student("李四cc", (byte) 1, "18578223322", (short) 20)));
        employeeDao.addBatchRollback(Arrays.asList(new Student("李四baa", (byte) 1, "18578223322", (short) 20), new Student("李四cc111111111111111111111111", (byte) 1, "18578223322", (short) 20)));
//        employeeDao.addBatchPoint(Arrays.asList(new Student("李四aa", (byte) 1, "18578223322", (short) 20), new Student("李四bb", (byte) 1, "18578223322", (short) 20), new Student("李四cc111111111111111111111111", (byte) 1, "18578223322", (short) 20)));
    }
}
